﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using UCNKompetenceBors.Model;

namespace UCNKompetenceBors.Database
{
    public class dbNews
    {
        /// <summary>
        /// Gets the latest news article.
        /// </summary>
        /// <returns>Returns a News object.</returns>
        public static News GetLatestNewsArticle()
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    News news = null;

                    string query = "select top 1 nw.newsID, nw.newsTitle, nw.newsText, nw.newsImage, nw.newsThumbnail, nw.newsCreatedDate, nw.newsUserID " +
                                   "from News nw " +
                                   "order by nw.newsCreatedDate desc";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        news = new News(Convert.ToInt32(dr["newsID"]));
                        news.Title = dr["newsTitle"].ToString();

                        news.Text = dr["newsText"].ToString();
                        news.CreatedDate = Convert.ToDateTime(dr["newsCreatedDate"]);
                        news.User = dbLogin.GetUserById(Guid.Parse(dr["newsUserID"].ToString()));

                        if (dr["newsImage"] != null)
                        {
                            news.ImageURL = dr["newsImage"].ToString();
                            news.ThumbnailURL = dr["newsThumbnail"].ToString();
                        }
                    }

                    con.Close();
                    con.Dispose();

                    return news;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets the latest localized news article, based on the culture parameter.
        /// </summary>
        /// <param name="culture">Specifies the culture to be used.</param>
        /// <returns>Returns a News object.</returns>
        public static News GetLatestNewsArticle(string culture)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    News news = null;

                    string query = "select top 1 nw.newsID, loc.newsLocalizedTitle, loc.newsLocalizedText, nw.newsImage, nw.newsThumbnail, nw.newsCreatedDate, nw.newsUserID " +
                                   "from News nw, newsLocalized loc " +
                                   "where loc.newsLocale = '" + culture + "' " +
                                   "and loc.newsID = nw.newsID " +
                                   "order by nw.newsCreatedDate desc";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        news = new News(Convert.ToInt32(dr["newsID"]));
                        news.Title = dr["newsLocalizedTitle"].ToString();

                        news.Text = dr["newsLocalizedText"].ToString();
                        news.CreatedDate = Convert.ToDateTime(dr["newsCreatedDate"]);
                        news.User = dbLogin.GetUserById(Guid.Parse(dr["newsUserID"].ToString()));

                        if (dr["newsImage"] != null)
                        {
                            news.ImageURL = dr["newsImage"].ToString();
                            news.ThumbnailURL = dr["newsThumbnail"].ToString();
                        }
                    }

                    con.Close();
                    con.Dispose();

                    return news;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets the 3 latest news articles.
        /// </summary>
        /// <returns>Returns a list of News objects.</returns>
        public static List<News> GetTop3News()
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<News> newsList = new List<News>();

                    string query = "select top 3 nw.newsID, nw.newsTitle, nw.newsText, nw.newsImage, nw.newsThumbnail, nw.newsCreatedDate, nw.newsUserID " +
                                   "from News nw " +
                                   "order by nw.newsCreatedDate desc";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        News news = new News(Convert.ToInt32(dr["newsID"]));
                        news.Title = dr["newsTitle"].ToString();

                        string newsText = GeneralTools.TruncateString(dr["newsText"].ToString(), 600, ".");

                        news.Text = newsText;
                        news.CreatedDate = Convert.ToDateTime(dr["newsCreatedDate"]);
                        news.User = dbLogin.GetUserById(Guid.Parse(dr["newsUserID"].ToString()));

                        if (dr["newsImage"] != null)
                        {
                            news.ImageURL = dr["newsImage"].ToString();
                            news.ThumbnailURL = dr["newsThumbnail"].ToString();
                        }

                        newsList.Add(news);
                    }

                    con.Close();
                    con.Dispose();

                    return newsList;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets the 3 latest localized news articles, based on the culture parameter.
        /// </summary>
        /// <param name="culture">Specifies the culture to be used.</param>
        /// <returns>Returns a list of News objects.</returns>
        public static List<News> GetTop3News(string culture)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<News> newsList = new List<News>();

                    string query = "select top 3 nw.newsID, loc.newsLocalizedTitle, loc.newsLocalizedText, nw.newsImage, nw.newsThumbnail, nw.newsCreatedDate, nw.newsUserID " +
                                   "from News nw, NewsLocalized loc " +
                                   "where loc.newsLocale= '" + culture + "' " +
                                   "and loc.newsID = nw.newsID " +
                                   "order by nw.newsCreatedDate desc";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        News news = new News(Convert.ToInt32(dr["newsID"]));
                        news.Title = dr["newsLocalizedTitle"].ToString();

                        string newsText = GeneralTools.TruncateString(dr["newsLocalizedText"].ToString(), 600, ".");

                        news.Text = newsText;
                        news.CreatedDate = Convert.ToDateTime(dr["newsCreatedDate"]);
                        news.User = dbLogin.GetUserById(Guid.Parse(dr["newsUserID"].ToString()));

                        if (dr["newsImage"] != null)
                        {
                            news.ImageURL = dr["newsImage"].ToString();
                            news.ThumbnailURL = dr["newsThumbnail"].ToString();
                        }

                        newsList.Add(news);
                    }

                    con.Close();
                    con.Dispose();

                    return newsList;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a list of all news offset by 1.
        /// </summary>
        /// <returns>Returns a list of News objects.</returns>
        public static List<News> GetAllNewsWithOffset()
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<News> newsList = new List<News>();

                    string query = "select newsID, newsTitle, newsText, newsImage, newsThumbnail, newsCreatedDate, newsUserID " +
                                   "from (" +
                                          "select newsID, newsTitle, newsText, newsImage, newsThumbnail, newsCreatedDate, newsUserID, ROW_NUMBER() OVER (ORDER BY newsCreatedDate desc) AS rownum " +
                                          "from News " +
                                        ") as t " +
                                   "where  t.rownum > 1";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        News news = new News(Convert.ToInt32(dr["newsID"]));
                        news.Title = dr["newsTitle"].ToString();

                        string newsText = GeneralTools.TruncateString(dr["newsText"].ToString(), 600, ".");

                        news.Text = newsText;
                        news.CreatedDate = Convert.ToDateTime(dr["newsCreatedDate"]);
                        news.User = dbLogin.GetUserById(Guid.Parse(dr["newsUserID"].ToString()));

                        if (dr["newsImage"] != null)
                        {
                            news.ImageURL = dr["newsImage"].ToString();
                            news.ThumbnailURL = dr["newsThumbnail"].ToString();
                        }

                        newsList.Add(news);
                    }

                    con.Close();
                    con.Dispose();

                    return newsList;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a list of all localized news offset by 1, based on the culture parameter.
        /// </summary>
        /// <param name="culture">Specifies the culture to be used.</param>
        /// <returns>Returns a list of News objects.</returns>
        public static List<News> GetAllNewsWithOffset(string culture)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<News> newsList = new List<News>();

                    string query = "select newsID, newsLocalizedTitle, newsLocalizedText, newsImage, newsThumbnail, newsCreatedDate, newsUserID " +
                                   "from (" +
                                            "select nw.newsID, loc.newsLocalizedTitle, loc.newsLocalizedText, nw.newsImage, nw.newsThumbnail, nw.newsCreatedDate, nw.newsUserID, ROW_NUMBER() OVER (ORDER BY nw.newsCreatedDate desc) AS rownum " +
                                            "from News nw, NewsLocalized loc " +
                                            "where loc.newsLocale= '" + culture + "' " +
                                            "and loc.newsID = nw.newsID " +
                                        ") as t " +
                                   "where  t.rownum > 1 ";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        News news = new News(Convert.ToInt32(dr["newsID"]));
                        news.Title = dr["newsLocalizedTitle"].ToString();

                        string newsText = GeneralTools.TruncateString(dr["newsLocalizedText"].ToString(), 600, ".");

                        news.Text = newsText;
                        news.CreatedDate = Convert.ToDateTime(dr["newsCreatedDate"]);
                        news.User = dbLogin.GetUserById(Guid.Parse(dr["newsUserID"].ToString()));

                        if (dr["newsImage"] != null)
                        {
                            news.ImageURL = dr["newsImage"].ToString();
                            news.ThumbnailURL = dr["newsThumbnail"].ToString();
                        }

                        newsList.Add(news);
                    }

                    con.Close();
                    con.Dispose();

                    return newsList;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a list of all news articles.
        /// </summary>
        /// <returns>Returns a list of News objects.</returns>
        public static List<News> GetAllNews()
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<News> newsList = new List<News>();

                    string query = "select nw.newsID, nw.newsTitle, nw.newsText, nw.newsImage, nw.newsThumbnail, nw.newsCreatedDate, nw.newsUserID " +
                                   "from News nw " +
                                   "order by nw.newsCreatedDate desc";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        News news = new News(Convert.ToInt32(dr["newsID"]));
                        news.Title = dr["newsTitle"].ToString();

                        string newsText = GeneralTools.TruncateString(dr["newsText"].ToString(), 600, ".");

                        news.Text = newsText;
                        news.CreatedDate = Convert.ToDateTime(dr["newsCreatedDate"]);
                        news.User = dbLogin.GetUserById(Guid.Parse(dr["newsUserID"].ToString()));

                        if (dr["newsImage"] != null)
                        {
                            news.ImageURL = dr["newsImage"].ToString();
                            news.ThumbnailURL = dr["newsThumbnail"].ToString();
                        }

                        newsList.Add(news);
                    }

                    con.Close();
                    con.Dispose();

                    return newsList;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a news article, based on the newsId parameter.
        /// </summary>
        /// <param name="newsId">Specifies the ID of the news article to be retrieved.</param>
        /// <returns>Returns a News object.</returns>
        public static News GetNewsById(int newsId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    News news = null;

                    string query = "select nw.newsTitle, nw.newsText, nw.newsImage, nw.newsThumbnail, nw.newsCreatedDate, nw.newsUserID, loc.newsLocalizedTitle, loc.newsLocalizedText " +
                                   "from News nw, NewsLocalized loc " +
                                   "where nw.newsID = @newsID " +
                                   "and loc.newsID = nw.newsID";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@newsId", newsId);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        news = new News(newsId);
                        news.Title = dr["newsTitle"].ToString();

                        news.Text = dr["newsText"].ToString();
                        news.CreatedDate = Convert.ToDateTime(dr["newsCreatedDate"]);
                        news.User = dbLogin.GetUserById(Guid.Parse(dr["newsUserID"].ToString()));

                        if (dr["newsImage"] != null)
                        {
                            news.ImageURL = dr["newsImage"].ToString();
                            news.ThumbnailURL = dr["newsThumbnail"].ToString();
                        }

                        news.LocalizedTitle = dr["newsLocalizedTitle"].ToString();
                        news.LocalizedText = dr["newsLocalizedText"].ToString();
                    }

                    con.Close();
                    con.Dispose();

                    return news;
                }

            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a localized news article, based on the newsId and culture parameters.
        /// </summary>
        /// <param name="newsId">Specifies the ID of the news article to be retrieved.</param>
        /// <param name="culture">Specifies the culture to be used.</param>
        /// <returns>Returns a News object.</returns>
        public static News GetNewsById(int newsId, string culture)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    News news = null;

                    string query = "select loc.newsLocalizedTitle, loc.newsLocalizedText, nw.newsImage, nw.newsThumbnail, nw.newsCreatedDate, nw.newsUserID " +
                                   "from News nw, NewsLocalized loc " +
                                   "where loc.newsLocale = '" + culture + "' " +
                                   "and nw.newsID = @newsID " +
                                   "and loc.newsID = nw.newsID";
                        

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@newsId", newsId);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        news = new News(newsId);
                        news.Title = dr["newsLocalizedTitle"].ToString();

                        string newsText = GeneralTools.TruncateString(dr["newsLocalizedText"].ToString(), 600, ".");

                        news.Text = newsText;
                        news.CreatedDate = Convert.ToDateTime(dr["newsCreatedDate"]);
                        news.User = dbLogin.GetUserById(Guid.Parse(dr["newsUserID"].ToString()));

                        if (dr["newsImage"] != null)
                        {
                            news.ImageURL = dr["newsImage"].ToString();
                            news.ThumbnailURL = dr["newsThumbnail"].ToString();
                        }
                    }

                    con.Close();
                    con.Dispose();

                    return news;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Deletes a news article, based on the newsId parameter.
        /// </summary>
        /// <param name="newsId">Specifies the ID of the news article to be deleted.</param>
        public static void DeleteNews(int newsId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "begin transaction " +
                                   "declare @rowsDeleted  int " +
                                   "delete from NewsLocalized " +
                                   "where newsID = @newsID " +
                                   "delete from News " +
                                   "where newsID = @newsID " +
                                   "set @rowsDeleted = @@ROWCOUNT " +
                                   "if @@ERROR <> 0 or @rowsDeleted = 0 " +
                                   "begin " +
                                   "rollback transaction " +
                                   "end " +
                                   "if @@TRANCOUNT > 0 " +
                                   "commit transaction";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@newsID", newsId);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Checks whether or not a news article has an image, based on the newsId parameter.
        /// </summary>
        /// <param name="newsId">Specifies the ID of the news article to be used.</param>
        /// <returns>Returns a boolean, specifying whether or not a news article has an image.</returns>
        public static bool NewsHasImage(int newsId)
        {
            try
            {
                bool result = false;
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "select n.newsImage " +
                                   "from News n " +
                                   "where n.newsID = @newsID";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@newsID", newsId);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        if (dr["newsImage"].ToString() != "~/Images/News/defaultNews.jpg")
                            result = true;
                    }

                    con.Close();
                    con.Dispose();

                    return result;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Creates a news article, based on the input parameters.
        /// </summary>
        /// <param name="title">Specifies the title to be used.</param>
        /// <param name="text">Specifies the text to be used.</param>
        /// <param name="hasImage">Specifies whether or not news article has an image.</param>
        /// <param name="imageFileName">Specifies the file name of the image to be used.</param>
        /// <param name="culture">Specifies the culture to be used in the localized news article.</param>
        /// <param name="locTitle">Specifies the title to be used in the localized news article.</param>
        /// <param name="locText">Specifies the text to be used in the localized news article.</param>
        public static void CreateNews(string title, string text, bool hasImage, string imageFileName, string culture, string locTitle, string locText)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "insert into News (newsTitle, newsText, newsImage, newsThumbnail, newsUserID, newsCreatedDate) " + 
                                   "values (@newsTitle, @newsText, @newsImage, @newsThumbnail, @newsUserID, CURRENT_TIMESTAMP) " +
                                   "insert into NewsLocalized (newsLocale, newsLocalizedTitle, newsLocalizedText) " +
                                   "values (@newsLocale, @newsLocalizedTitle, @newsLocalizedText)";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    MembershipUser mu = Membership.GetUser();
                    cmd.Parameters.AddWithValue("@newsTitle", title);

                    string newsText = text.Replace(Environment.NewLine, "<br />");

                    cmd.Parameters.AddWithValue("@newsText", newsText);

                    if (hasImage)
                    {
                        cmd.Parameters.AddWithValue("@newsImage", "~/Images/News/" + title.Trim() + "_" + DateTime.Now.ToShortDateString() + "/" + imageFileName);
                        cmd.Parameters.AddWithValue("@newsThumbnail", "~/Images/News/" + title.Trim() + "_" + DateTime.Now.ToShortDateString() + "/thumb_" + imageFileName);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@newsImage", "~/Images/News/defaultNews.jpg");
                        cmd.Parameters.AddWithValue("@newsThumbnail", "~/Images/News/thumb_defaultNews.jpg");
                    }

                    cmd.Parameters.AddWithValue("@newsUserID", mu.ProviderUserKey);

                    cmd.Parameters.AddWithValue("@newsLocale", culture);

                    cmd.Parameters.AddWithValue("@newsLocalizedTitle", locTitle);

                    cmd.Parameters.AddWithValue("@newsLocalizedText", locText);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Edits a news article, based on the newsId parameter.
        /// </summary>
        /// <param name="title">Specifies the title to be used.</param>
        /// <param name="text">Specifies the text to be used.</param>
        /// <param name="hasImage">Specifies whether or not news article has an image.</param>
        /// <param name="imagePath">Specifies the path of the current image.</param>
        /// <param name="imageFileName">Specifies the file name of the image to be used.</param>
        /// <param name="newsId">Specifies the ID of the news article to be used.</param>
        /// <param name="locTitle">Specifies the title to be used in the localized news article.</param>
        /// <param name="locText">Specifies the text to be used in the localized news article.</param>
        public static void EditNews(string title, string text, bool hasImage, string imagePath, string imageFileName, int newsId, string locTitle, string locText)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {

                    string query = "update News " +
                                   "set newsTitle = @newsTitle, newsText = @newsText ";

                    if (hasImage)
                    {
                        query += ", newsImage = @newsImage, newsThumbnail = @newsThumbnail";
                    }
                    
                    query += " where newsID = @newsID";

                    query += " update NewsLocalized " +
                             "set newsLocalizedTitle = @newsLocalizedTitle, newsLocalizedText = @newsLocalizedText " +
                             "where newsID = @newsID";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@newsTitle", title);

                    text = text.Replace(Environment.NewLine, "<br />");

                    cmd.Parameters.AddWithValue("@newsText", text);

                    if (hasImage)
                    {
                        cmd.Parameters.AddWithValue("@newsImage", imagePath + "\\" + imageFileName);
                        cmd.Parameters.AddWithValue("@newsThumbnail", imagePath + "\\thumb_" + imageFileName);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@newsImage", "~/Images/News/defaultNews.jpg");
                        cmd.Parameters.AddWithValue("@newsThumbnail", "~/Images/News/thumb_defaultNews.jpg");
                    }

                    cmd.Parameters.AddWithValue("@newsID", newsId);

                    cmd.Parameters.AddWithValue("@newsLocalizedTitle", locTitle);

                    cmd.Parameters.AddWithValue("@newsLocalizedText", locText);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }
    }
}